import org.apache.spark.sql.{DataFrame, SparkSession}

object DataQuery {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder.appName("SparkSQL-DataQuery").master("local[*]").getOrCreate()
    val studentsDF = spark.read
      .format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/sparksql_practice")
      .option("dbtable", "students")
      .option("user", "your_username")
      .option("password", "your_password")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .load()
    val olderThan20 = studentsDF.filter($"age" > 20)
    olderThan20.show()

    spark.stop()
  }
}
val sortedStudents = studentsDF.filter($"major" === "Computer Science").orderBy($"enrollment_date".asc)
sortedStudents.show()
val majorCounts = studentsDF.groupBy("major").count()
majorCounts.show()
